package com.eascs.web.o2o.web.bi.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eascs.web.o2o.web.bi.dao.BIDao;
import com.eascs.web.o2o.web.bi.entity.BICost;

/**
 * 费用信息（保证金、管理费、风险补偿金）
 * 
 * @author ycz
 *
 */
@Service
public class CostService {
	@Autowired
	private BIDao biDao;
	
	public List<BICost> queryCost() {
		String sql = " SELECT t1.regional_name AS provincial, SUM( IFNULL(t2.red_deposite_mon, 0)) AS redDepositeMon, SUM( IFNULL(t2.red_manger_mon, 0)) AS redMangerMon, SUM(IFNULL(t2.red_risk_mon, 0)) AS redRiskMon, SUM( IFNULL(t2.reta_deposite_mon, 0)) AS retaDepositeMon, SUM( IFNULL(t2.red_deposite_years, 0)) AS redDepositeYears, SUM( IFNULL(t2.red_manger_years, 0)) AS redManagerYears, SUM( IFNULL(t2.red_risk_years, 0)) AS redRiskYears, SUM( IFNULL(t2.reta_deposite_years, 0)) AS retaDepositeYears FROM ( SELECT regional_name FROM o2o_platform_regional GROUP BY regional_name ) t1 LEFT JOIN ( SELECT r.regional_name, y.ltd, SUM( IFNULL(m.red_deposite_mon, 0)) AS red_deposite_mon, SUM(IFNULL(m.red_manger_mon, 0)) AS red_manger_mon, SUM(IFNULL(m.red_risk_mon, 0)) AS red_risk_mon, SUM( IFNULL(m.reta_deposite_mon, 0)) AS reta_deposite_mon, SUM( IFNULL(y.red_deposite_years, 0)) AS red_deposite_years, SUM( IFNULL(y.red_manger_years, 0)) AS red_manger_years, SUM(IFNULL(y.red_risk_years, 0)) AS red_risk_years, SUM( IFNULL(y.reta_deposite_years, 0)) AS reta_deposite_years FROM ( SELECT ol.ltd, ole.loanapply_no, IFNULL(ole.red_deposite, 0) AS red_deposite_years, IFNULL(ole.red_manger, 0) AS red_manger_years, IFNULL(ole.red_risk, 0) AS red_risk_years, IFNULL(ole.reta_deposite, 0) AS reta_deposite_years FROM o2o_loanapply_expand ole LEFT JOIN o2o_loanapply ol ON ole.loanapply_no = ol.loanapply_no WHERE ole.creat_time >= DATE_FORMAT(NOW(), '%Y')) y LEFT JOIN ( SELECT ole.loanapply_no, IFNULL(ole.red_deposite, 0) AS red_deposite_mon, IFNULL(ole.red_manger, 0) AS red_manger_mon, IFNULL(ole.red_risk, 0) AS red_risk_mon, IFNULL(ole.reta_deposite, 0) AS reta_deposite_mon FROM o2o_loanapply_expand ole LEFT JOIN o2o_loanapply ol ON ole.loanapply_no = ol.loanapply_no WHERE ole.creat_time >= DATE_FORMAT(NOW(), '%Y-%m')) m ON y.loanapply_no = m.loanapply_no LEFT JOIN o2o_platform_regional r ON y.ltd = r.platform_id GROUP BY r.regional_name ) t2 ON t1.regional_name = t2.regional_name GROUP BY t1.regional_name ORDER BY CONVERT (t1.regional_name USING gbk) ASC ";
		return biDao.findEntitys(sql, BICost.class);
	}
	
}
